package com.baomidou.springwind.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.springwind.entity.*;
import com.baomidou.springwind.mapper.*;
import com.baomidou.springwind.service.*;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * excel文件读取
 *
 * @author luox
 */
@Component
public class OrderDataDataReader extends ExcelReader<OrderData> {

    @Autowired
    private OrderDataDao orderDataDao;//
    @Autowired
    private NetworkFactoryDao networkFactoryDao;//
    @Autowired
    private NetworkFactoryGoodsDao networkFactoryGoodsDao;//
    @Autowired
    private NetworkLngLatDao networkLngLatDao;//地区Dao
    @Autowired
    private NetworkCustomerDao networkCustomerDao;//地区Dao
    @Autowired
    private NetworkDepotGoodsDao networkDepotGoodsDao;//地区Dao
    @Autowired
    private NetworkCustomerGoodsDao networkCustomerGoodsDao;//地区Dao
    @Autowired
    private NetworkDepotDao networkDepotDao;//地区Dao
    @Autowired
    private SysAreaDao sysAreaDao;//地区Dao
    @Autowired
    private OrderDataService orderDataService;
    @Autowired
    private NetworkLngLatService networkLngLatService;
    @Autowired
    private NetworkCustomerGoodsService networkCustomerGoodsService;
    @Autowired
    private NetworkFactoryGoodsService networkFactoryGoodsService;
    @Autowired
    private NetworkTransportPropertyService networkTransportPropertyService;

    @Override
    protected String saveData(List<OrderData> dataList, Long batchId) {
        EntityWrapper<OrderData> wrapper = new EntityWrapper<OrderData>();
        wrapper.eq("batch_id", batchId);
        deleteInventoryCostFile(batchId);
        orderDataService.delete(wrapper);//删除之前导入的数据
        for (int i=0;i<dataList.size();i++) {
            OrderData orderData=dataList.get(i);
            orderData.setBatchId(batchId);
            orderData.setCreateTime(new Date());
            orderData.setOperatorId(getCurrentUserId());
            if(orderData.getFactoryName().isEmpty()==true||orderData.getBranchCenter().isEmpty()==true||orderData.getDeliveryCenter().isEmpty()==true||orderData.getCustomer().isEmpty()==true){
                return "上传的excel的第"+(i+2)+"行数据是空的，请填写完数据以后，重新上传";
            }
        }
        orderDataService.insertBatch(dataList, 100);
       // List<OrderData> orderDataList=orderDataService.selectList(wrapper);
       // updateId(orderDataList);NetworkFactoryGoodService
        buildFatory(batchId);
        String str1=bulicLng(batchId);
        if(str1.length()>0){
            return str1;
        }
        updateLng(batchId);
        buildStandard(batchId);
        insertFalse(batchId);
        return "";
    }

    /**
     * 模拟：插入用户填写的输入数据
     * @param batchId
     */
    public void insertFalse(Long batchId){
     //   factoryGoodsFalse(batchId);
    //    transportPropertyFalse(batchId);
    }

    /**
     * 模拟：运输属性数据
     * @param batchId
     */
    public void transportPropertyFalse(Long batchId){
        Random random=new Random(20);
        List<NetworkTransportProperty> list0 = new ArrayList<NetworkTransportProperty>();

        EntityWrapper<NetworkFactory> wrapper1 = new EntityWrapper<NetworkFactory>();
        wrapper1.eq("batch_id", batchId);
        List<NetworkFactory> list1 = networkFactoryDao.selectList(wrapper1);

        EntityWrapper<NetworkDepot> wrapper2 = new EntityWrapper<NetworkDepot>();
        wrapper2.eq("batch_id", batchId);
        wrapper2.eq("depot_level",1);
        List<NetworkDepot> list2 = networkDepotDao.selectList(wrapper2);

        EntityWrapper<NetworkDepotGoods> wrapper21 = new EntityWrapper<NetworkDepotGoods>();
        wrapper21.eq("batch_id", batchId);
        wrapper21.eq("type",1);
        List<NetworkDepotGoods> list21 = networkDepotGoodsDao.selectList(wrapper21);

        EntityWrapper<NetworkDepot> wrapper3 = new EntityWrapper<NetworkDepot>();
        wrapper3.eq("batch_id", batchId);
        wrapper3.eq("depot_level",2);
        List<NetworkDepot> list3 = networkDepotDao.selectList(wrapper3);

        EntityWrapper<NetworkDepotGoods> wrapper31 = new EntityWrapper<NetworkDepotGoods>();
        wrapper31.eq("batch_id", batchId);
        wrapper31.eq("type",2);
        List<NetworkDepotGoods> list31 = networkDepotGoodsDao.selectList(wrapper31);


        EntityWrapper<NetworkCustomerGoods> wrapper4 = new EntityWrapper<NetworkCustomerGoods>();
        wrapper4.eq("batch_id", batchId);
        List<NetworkCustomerGoods> list4 = networkCustomerGoodsDao.selectList(wrapper4);

        EntityWrapper<NetworkTransportProperty> wrapper5 = new EntityWrapper<NetworkTransportProperty>();
        wrapper5.eq("batch_id", batchId);
        networkTransportPropertyService.delete(wrapper5);

        String start="";
        String end="";
        String good="";
        NetworkTransportProperty property;
        for(int i=0;i<list1.size();i++){
            start=list1.get(i).getFactoryName();
            for (int j=0;j<list21.size();j++){
                end=list21.get(j).getDepotName();
                good=list21.get(j).getGoodName();
                property=new NetworkTransportProperty();
                property.setShippingMethod("汽车");
                property.setGoodName(good);
                property.setStartingPoint(start);
                property.setEndPoint(end);
                property.setTransportRate(random.nextDouble());
                property.setType(1);
                property.setBatchId(batchId);
                list0.add(property);
            }
        }
        for(int i=0;i<list2.size();i++){
            start=list2.get(i).getDepotName();
            for (int j=0;j<list31.size();j++){
                end=list31.get(j).getDepotName();
                good=list31.get(j).getGoodName();
                property=new NetworkTransportProperty();
                property.setShippingMethod("汽车");
                property.setTransportRate(random.nextDouble());
                property.setStartingPoint(start);
                property.setEndPoint(end);
                property.setGoodName(good);
                property.setType(2);
                property.setBatchId(batchId);
                list0.add(property);
            }
        }
        for (int i=0;i<list3.size();i++){
            start=list3.get(i).getDepotName();
            for (int j=0;j<list4.size();j++){
                end=list4.get(j).getCustomerName();
                good=list4.get(j).getGoodsName();
                property=new NetworkTransportProperty();
                property.setShippingMethod("汽车");
                property.setTransportRate(random.nextDouble());
                property.setStartingPoint(start);
                property.setEndPoint(end);
                property.setGoodName(good);
                property.setType(3);
                property.setBatchId(batchId);
                list0.add(property);
            }
        }
        networkTransportPropertyService.insertBatch(list0,100);
    }


    /**
     * 模拟：插入工厂商品数据中的单位成本
     * @param batchId
     */
    public void factoryGoodsFalse(Long batchId){
        Random random=new Random(20);
        EntityWrapper<NetworkFactoryGoods> wrapper0 = new EntityWrapper<NetworkFactoryGoods>();
        wrapper0.eq("batch_id", batchId);
        List<NetworkFactoryGoods> list0 = networkFactoryGoodsDao.selectList(wrapper0);
        for (int i=0;i<list0.size();i++){
            list0.get(i).setGoodCost(random.nextDouble());
        }
        networkFactoryGoodsDao.delete(wrapper0);
        networkFactoryGoodsService.insertBatch(list0,100);
    }

    /**
     * 插入用户订单表的标准差数据
     * @param batchId
     */
    public void buildStandard(Long batchId){
        EntityWrapper<NetworkCustomerGoods> wrapper0 = new EntityWrapper<NetworkCustomerGoods>();
        wrapper0.eq("batch_id", batchId);
        wrapper0.groupBy("goods_name");
        wrapper0.groupBy("customer_id");
        List<NetworkCustomerGoods> list0 = networkCustomerGoodsDao.selectList(wrapper0);
        final Long batchIds=batchId;
        final List<NetworkCustomerGoods> list00=new ArrayList<NetworkCustomerGoods>();

        for ( NetworkCustomerGoods list1:list0){
                    EntityWrapper<NetworkCustomerGoods> wrapper1 = new EntityWrapper<NetworkCustomerGoods>();
                    wrapper1.eq("batch_id", batchIds);
                    wrapper1.eq("goods_name",list1.getGoodsName());
                    wrapper1.eq("customer_id",list1.getCustomerId());
                    List<NetworkCustomerGoods> list2 = networkCustomerGoodsDao.selectList(wrapper1);
                    double avg=avg(list2);
                    double standard=standard(avg,list2);
                    list1.setDemandNumber(avg);
                    list1.setStandardDeviation(standard);
        }
        deleteCustomerGoods(batchId);
        networkCustomerGoodsService.insertBatch(list0,100);
    }

    /**
     * 删除以前的batchId的数据
     * @param batchId
     */
    public void deleteCustomerGoods(Long batchId){
        EntityWrapper<NetworkCustomerGoods> wrapper0 = new EntityWrapper<NetworkCustomerGoods>();
        wrapper0.eq("batch_id", batchId);
        networkCustomerGoodsDao.delete(wrapper0);
    }

    /**
     * 返回标准差
     * @param avg
     * @param list2
     * @return
     */
    public double standard(double avg,List<NetworkCustomerGoods> list2){
        double sum=0;
        for (int i=0;i<list2.size();i++){
            sum=sum+(list2.get(i).getDemandNumber()-avg)*(list2.get(i).getDemandNumber()-avg);
        }
        return Math.sqrt(sum/list2.size());
    }

    /**
     * 计算平均值
     * @param list2
     * @return
     */
    public double avg(List<NetworkCustomerGoods> list2){
        double sum=0;
        for (int i=0;i<list2.size();i++){
            sum=sum+list2.get(i).getDemandNumber();
        }
        return sum/list2.size();
    }




    /**
     * 为没有经纬度的，通过百度地图查询出经纬度
     * @param batchId
     */
    public String bulicLng(Long batchId){
        EntityWrapper<NetworkLngLat> wrapper0 = new EntityWrapper<NetworkLngLat>();
        wrapper0.eq("batch_id", batchId);
        wrapper0.eq("type",0);
        List<NetworkLngLat> list = networkLngLatDao.selectList(wrapper0);

        for (int i=0;i<list.size();i++){
            JSONArray jsonArray=latlng(list.get(i).getName());
            if(jsonArray.get(0).toString().length()==1){
                return "excel表数据的"+list.get(i).getName()+"百度查询不到经纬度，请修改地址名称。";
            }
            list.get(i).setLng(jsonArray.get(0).toString());
            list.get(i).setLat(jsonArray.get(1).toString());
        }
        networkLngLatDao.delete(wrapper0);
        if(list.size()>0){
            networkLngLatService.insertBatch(list);
        }
        return "";
    }

    /**
     * 分为6个表保存到数据库
     * @param batchId
     */
    public void buildFatory(Long batchId){
        OrderData orderData=new OrderData();
        orderData.setBatchId(batchId);
        orderDataDao.buildGoods(orderData);
    }



    /**
     * 查询lng_lat表，查询batchId里面没有经纬度的
     * @param batchId
     */
    public void LngLat(Long batchId){
        EntityWrapper<NetworkLngLat> wrapper = new EntityWrapper<NetworkLngLat>();
        wrapper.eq("batch_id", batchId);
        wrapper.eq("type",0);
        List<NetworkLngLat> list0=networkLngLatDao.selectList(wrapper);
       // updateLng(list0);
    }

    /**
     * 赋予经纬度
     * @param batchId
     */
    public void updateLng(Long batchId){
        List<NetworkCustomer> list0=getCustomer(batchId);
        List<NetworkDepot> list1= getDepot(batchId);
        List<NetworkFactory> list2= getFactory(batchId);
        List<String> sql=new ArrayList<String>();
        for(int i=0;i<list0.size();i++){
            sql.add("UPDATE glc_network_customer a SET a.customer_lng = (SELECT b.lng FROM sys_area b WHERE b.name= "+" '"+list0.get(i).getCustomerName()+"'" +"LIMIT 1"
                    + "),a.customer_lat = (SELECT b.lat FROM sys_area b WHERE b.name="+" '"+ list0.get(i).getCustomerName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and  a.customer_lng is null AND a.customer_name="+" '"+list0.get(i).getCustomerName()+"'"+ ";");
            sql.add("UPDATE glc_network_customer a SET a.customer_lng = (SELECT b.lng FROM glc_network_lng_lat b WHERE b.name= "+" '"+list0.get(i).getCustomerName()+"'" +"LIMIT 1"
                    + "),a.customer_lat = (SELECT b.lat FROM glc_network_lng_lat b WHERE b.name="+ " '"+list0.get(i).getCustomerName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and  a.customer_lng is null AND a.customer_name="+" '"+list0.get(i).getCustomerName()+"'"+";");
        }

        for(int i=0;i<list1.size();i++){
            sql.add("UPDATE glc_network_depot a SET a.depot_lng = (SELECT b.lng FROM sys_area b WHERE b.name= "+" '"+list1.get(i).getDepotName()+"'" +"LIMIT 1"
                    + "),a.depot_lat = (SELECT b.lat FROM sys_area b WHERE b.name="+ " '"+list1.get(i).getDepotName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and a.depot_lng is null  AND a.depot_name="+" '"+list1.get(i).getDepotName()+"'"+";");
            sql.add("UPDATE glc_network_depot a SET a.depot_lng = (SELECT b.lng FROM glc_network_lng_lat b WHERE b.name= "+" '"+list1.get(i).getDepotName()+"'" +"LIMIT 1"
                    + "),a.depot_lat = (SELECT b.lat FROM glc_network_lng_lat b WHERE b.name="+ " '"+list1.get(i).getDepotName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and a.depot_lng is null  AND a.depot_name="+" '"+list1.get(i).getDepotName()+"'"+";");
        }
        for(int i=0;i<list2.size();i++){
            sql.add("UPDATE glc_network_factory a SET a.factory_lng = (SELECT b.lng FROM sys_area b WHERE b.name= "+" '"+list2.get(i).getFactoryName()+"'" +"LIMIT 1"
                    + "),a.factory_lat = (SELECT b.lat FROM sys_area b WHERE b.name="+ " '"+list2.get(i).getFactoryName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and a.factory_lng is null  AND a.factory_name="+" '"+list2.get(i).getFactoryName()+"'"+";");
            sql.add("UPDATE glc_network_factory a SET a.factory_lng = (SELECT b.lng FROM glc_network_lng_lat b WHERE b.name= "+" '"+list2.get(i).getFactoryName()+"'" +"LIMIT 1"
                    + "),a.factory_lat = (SELECT b.lat FROM glc_network_lng_lat b WHERE b.name="+ " '"+list2.get(i).getFactoryName()+"'" +"LIMIT 1"
                    +") WHERE a.batch_id="+batchId+" and a.factory_lng is null  AND a.factory_name="+" '"+list2.get(i).getFactoryName()+"'"+";");
        }
        updateLngs1(sql);


    }
    public  void  updateLngs1(List<String> sqls){
        for(int i=0;i<sqls.size();i++){
            orderDataDao.updateLng(sqls.get(i));
        }
    }

    public  void  updateLngs(List<String> sqls){
        ExecutorService fixThreadPool= Executors.newFixedThreadPool(10);
        for (final String sql:sqls){
            fixThreadPool.execute(new Runnable() {
                @Override
                public void run() {
                    orderDataDao.updateLng(sql);
                }
            });
        }
        fixThreadPool.shutdown();
    }


    /**
     *
     * @param batchId
     * @return 该id下的客户信息
     */
    public List<NetworkCustomer> getCustomer(Long batchId){
            EntityWrapper<NetworkCustomer> wrapper = new EntityWrapper<NetworkCustomer>();
            wrapper.eq("batch_id", batchId);
            List<NetworkCustomer> list0 = networkCustomerDao.selectList(wrapper);
            return list0;
    }
    /**
     *
     * @param batchId
     * @return 该id下的仓库信息
     */
    public List<NetworkDepot> getDepot(Long batchId){
        EntityWrapper<NetworkDepot> wrapper = new EntityWrapper<NetworkDepot>();
        wrapper.eq("batch_id", batchId);
        List<NetworkDepot> list0 = networkDepotDao.selectList(wrapper);
        return list0;
    }
    /**
     *
     * @param batchId
     * @return 该id下的工厂信息
     */
    public List<NetworkFactory> getFactory(Long batchId){
        EntityWrapper<NetworkFactory> wrapper = new EntityWrapper<NetworkFactory>();
        wrapper.eq("batch_id", batchId);
        List<NetworkFactory> list0 = networkFactoryDao.selectList(wrapper);
        return list0;
    }
    /**
     * 通过线程池来为地点赋值经纬度
     * @param orderDataList
     */
    public void updateId(List<OrderData> orderDataList){
        ExecutorService fixThreadPool= Executors.newFixedThreadPool(50);
        for (final OrderData orderData:orderDataList){
            fixThreadPool.execute(new Runnable() {
                @Override
                public void run() {
                    HashMap<String,Object> map1=lng1(orderData.getBranchCenter().toString().replace("工贸",""));
                    orderData.setBranchId(Long.valueOf(map1.get("id").toString()));
                    orderData.setBranchLng(map1.get("lng").toString());
                    orderData.setBranchLat(map1.get("lat").toString());

                    HashMap<String,Object> map2=lng2(orderData.getDeliveryCenter());
                    orderData.setDeliveryId(Long.valueOf(map2.get("id").toString()));
                    orderData.setDeliveryLng(map2.get("lng").toString());
                    orderData.setDeliveryLat(map2.get("lat").toString());

                    HashMap<String,Object> map3=lng3(orderData.getFactoryName());
                    orderData.setFactoryId(Long.valueOf(map3.get("id").toString()));
                    orderData.setFactoryLng(map3.get("lng").toString());
                    orderData.setFactoryLat(map3.get("lat").toString());

                    orderDataService.updateById(orderData);
                }
            });

        }
        fixThreadPool.shutdown();
    }
    public List<HashMap<String,Object>> branch(Long batchId){
        List<HashMap<String,Object>> list1 = new ArrayList<HashMap<String, Object>>();
        EntityWrapper<OrderData> wrapper = new EntityWrapper<OrderData>();
        wrapper.eq("batch_id", batchId);
        wrapper.groupBy("branch_center");
        List<OrderData> orderDataList=orderDataService.selectList(wrapper);
        for (int i=0;i<orderDataList.size();i++){
            //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
            HashMap<String,Object> map1=lng1(orderDataList.get(i).getBranchCenter().toString().replace("工贸",""));
            map1.put("branchCenter",orderDataList.get(i).getBranchCenter());
            map1.put("id", map1.get("id"));
            map1.put("lng",map1.get("lng"));
            map1.put("lat",map1.get("lat"));
            list1.add(map1);
        }
        return list1;
    }
    public List<HashMap<String,Object>> second(Long batchId){
        List<HashMap<String,Object>> list2 = new ArrayList<HashMap<String, Object>>();
        EntityWrapper<OrderData> wrapper = new EntityWrapper<OrderData>();
        wrapper.eq("batch_id", batchId);
        wrapper.groupBy("delivery_center");
        List<OrderData> orderDataList=orderDataService.selectList(wrapper);
        for (int i=0;i<orderDataList.size();i++){
            //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
            HashMap<String,Object> map1=lng2(orderDataList.get(i).getDeliveryCenter());
            map1.put("deliveryCenter",orderDataList.get(i).getDeliveryCenter());
            map1.put("id", map1.get("id"));
            map1.put("lng",map1.get("lng"));
            map1.put("lat",map1.get("lat"));
            list2.add(map1);
        }
        return list2;
    }
    public List<HashMap<String,Object>> factory(Long batchId){
        List<HashMap<String,Object>> list3 = new ArrayList<HashMap<String, Object>>();
        EntityWrapper<OrderData> wrapper = new EntityWrapper<OrderData>();
        wrapper.eq("batch_id", batchId);
        wrapper.groupBy("factory_name");
        List<OrderData> orderDataList=orderDataService.selectList(wrapper);
        for (int i=0;i<orderDataList.size();i++){
            //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
            HashMap<String,Object> map1=lng3(orderDataList.get(i).getFactoryName());
            map1.put("factoryName",orderDataList.get(i).getFactoryName());
            map1.put("id", map1.get("id"));
            map1.put("lng",map1.get("lng"));
            map1.put("lat",map1.get("lat"));
            list3.add(map1);
        }
        return list3;
    }
    public HashMap<String,Object> lng1(String name){
        HashMap<String,Object> map=new HashMap<String, Object>();
        //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
        EntityWrapper<NetworkLngLat> wrapperBranch = new EntityWrapper<NetworkLngLat>();
        wrapperBranch.eq("name", name);
        List<Map<String, Object>> networkLngLatBranch=networkLngLatDao.selectMaps(wrapperBranch);
        if (networkLngLatBranch.size()>0){
            map.put("id",networkLngLatBranch.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch.get(0).get("lat").toString());
        }else{
            JSONArray jsonArray=latlng(name);
            NetworkLngLat networkLngLatbran=new NetworkLngLat();
            networkLngLatbran.setName(name);
            networkLngLatbran.setLng(jsonArray.get(0).toString());
            networkLngLatbran.setLat(jsonArray.get(1).toString());
            networkLngLatDao.insert(networkLngLatbran);
            EntityWrapper<NetworkLngLat> wrapperBranch2 = new EntityWrapper<NetworkLngLat>();
            wrapperBranch2.eq("name", name);
            List<Map<String, Object>> networkLngLatBranch2=networkLngLatDao.selectMaps(wrapperBranch2);
            map.put("id",networkLngLatBranch2.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch2.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch2.get(0).get("lat").toString());
        }
        return map;
    }
    public HashMap<String,Object> lng2(String name){
        HashMap<String,Object> map=new HashMap<String, Object>();
        //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
        EntityWrapper<SysArea> wrapperDelivery = new EntityWrapper<SysArea>();
        wrapperDelivery.eq("level",2);
        wrapperDelivery.eq("shortname", name);
        List<Map<String, Object>> networkLngLatBranch=sysAreaDao.selectMaps(wrapperDelivery);
        if (networkLngLatBranch.size()>0){
            map.put("id",networkLngLatBranch.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch.get(0).get("lat").toString());
        }
        if(networkLngLatBranch.size()==0){
            EntityWrapper<NetworkLngLat> lngLatEntityWrapper = new EntityWrapper<NetworkLngLat>();
            lngLatEntityWrapper.eq("name",name);
            List<NetworkLngLat> lngLatList= networkLngLatDao.selectList(lngLatEntityWrapper);
            if(lngLatList.size()>0){
                map.put("id",lngLatList.get(0).getId());
                map.put("lng",lngLatList.get(0).getLng());
                map.put("lat",lngLatList.get(0).getLat());
            }
        }
        return map;
    }
    public HashMap<String,Object> lng3(String name){
        HashMap<String,Object> map=new HashMap<String, Object>();
        //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
        EntityWrapper<NetworkLngLat> wrapperFactory = new EntityWrapper<NetworkLngLat>();
        wrapperFactory.eq("name", name);
        List<Map<String, Object>> networkLngLatBranch=networkLngLatDao.selectMaps(wrapperFactory);
        if (networkLngLatBranch.size()>0){
            map.put("id",networkLngLatBranch.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch.get(0).get("lat").toString());
        }else{
            JSONArray jsonArray=latlng(name);
            NetworkLngLat networkLngLatbran=new NetworkLngLat();
            networkLngLatbran.setName(name);
            networkLngLatbran.setLng(jsonArray.get(0).toString());
            networkLngLatbran.setLat(jsonArray.get(1).toString());
            networkLngLatDao.insert(networkLngLatbran);
            EntityWrapper<NetworkLngLat> wrapperBranch2 = new EntityWrapper<NetworkLngLat>();
            wrapperBranch2.eq("name", name);
            List<Map<String, Object>> networkLngLatBranch2=networkLngLatDao.selectMaps(wrapperBranch2);
            map.put("id",networkLngLatBranch2.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch2.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch2.get(0).get("lat").toString());
        }
        return map;
    }



    private void deleteInventoryCostFile(Long batchId) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String path = request.getSession().getServletContext().getRealPath("");
        path = path.substring(0, path.lastIndexOf("\\"));
        path = path.substring(0, path.lastIndexOf("\\"));
        File file = new File(path + "/temp/route/" + batchId + ".obj");
        file.delete();
    }
    @Override
    protected List<OrderData> verifyData(HSSFWorkbook workbook) {
        List<OrderData> orderDataList = new ArrayList<OrderData>();
        HSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        OrderData orderData;
        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try{
            for (int i = 1; i <= lastRowNum; i++) {
                sheet.getRow(i);
                orderData = new OrderData();
                orderData.setOrderQuantity(Integer.valueOf(getStringCellValue(sheet.getRow(i), 0)));
                orderData.setSignQuantity(Integer.valueOf(getStringCellValue(sheet.getRow(i), 1)));
                orderData.setBranchCenter(getStringCellValue(sheet.getRow(i), 2));
                orderData.setDeliveryCenter(getStringCellValue(sheet.getRow(i), 3));
                orderData.setFactoryName(getStringCellValue(sheet.getRow(i), 4));
                orderData.setPromiseArrival(getDateCellValue(sheet.getRow(i), 5));
                orderData.setPreparationCar(getDateCellValue(sheet.getRow(i), 6));
                orderData.setExpectedCenter(getDateCellValue(sheet.getRow(i), 7));
                orderData.setCarArrive(getDateCellValue(sheet.getRow(i), 8));
                orderData.setGood(getStringCellValue(sheet.getRow(i), 9));
                orderData.setCustomer(getStringCellValue(sheet.getRow(i), 10));
                orderData.setOrderTime(getDateCellValue(sheet.getRow(i), 11));
                orderDataList.add(orderData);
            }
        }catch (Exception e){
            System.out.println("报错");
        }
        return orderDataList;
    }


    @Override
    protected List<OrderData> verifyData7(XSSFWorkbook workbook) {
        List<OrderData> orderDataList = new ArrayList<OrderData>();
        XSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        OrderData orderData;
        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String branch="";
        String delivery="";
        String factory="";
        String customer="";
        try{
            for (int i = 1; i <= lastRowNum; i++) {
                sheet.getRow(i);
                orderData = new OrderData();
                orderData.setOrderQuantity(Integer.valueOf(getStringCellValue7(sheet.getRow(i), 0)));
                orderData.setSignQuantity(Integer.valueOf(getStringCellValue7(sheet.getRow(i), 1)));
                branch=getStringCellValue7(sheet.getRow(i), 2);
                orderData.setBranchCenter(branch);
                delivery=getStringCellValue7(sheet.getRow(i), 3);
                orderData.setDeliveryCenter(delivery);
                factory=getStringCellValue7(sheet.getRow(i), 4);
                orderData.setFactoryName(factory);
                orderData.setPromiseArrival(getDateCellValue7(sheet.getRow(i), 5));
                orderData.setPreparationCar(getDateCellValue7(sheet.getRow(i), 6));
                orderData.setExpectedCenter(getDateCellValue7(sheet.getRow(i), 7));
                orderData.setCarArrive(getDateCellValue7(sheet.getRow(i), 8));
                orderData.setGood(getStringCellValue7(sheet.getRow(i), 9));
                customer=getStringCellValue7(sheet.getRow(i), 10);
                orderData.setCustomer(customer);
                orderData.setOrderTime(getDateCellValue7(sheet.getRow(i), 11));
                orderDataList.add(orderData);
            }
        }catch (Exception e){
            System.out.println("获取excel2007文档上传数据报错");
        }
        return orderDataList;
    }

    //通过地址名来获取到经纬度,调用百度地图来处理的
    private JSONArray latlng(String placename){
        JSONArray jsonArray=new JSONArray();
        String url = String.format("http://api.map.baidu.com/geocoder?address=%s&output=json&key=%s", URLEncoder.encode(placename.toString()),"NURx5GUAQ1IBZVBMCQSpYppRsDdTpbxG");
        CloseableHttpClient client = HttpClients.createDefault();
        double lng=0;
        double lat=0;
        try {
            HttpGet httpGet = new HttpGet(url);
            HttpResponse httpResponse = client.execute(httpGet);

            if (httpResponse.getStatusLine().getStatusCode() == 200) {
                HttpEntity httpEntity = httpResponse.getEntity();
                String text = EntityUtils.toString(httpEntity);//取出应答字符串
                JSONObject json = JSONObject.parseObject(text);
                System.out.println(placename);
                lng = json.getJSONObject("result").getJSONObject("location").getDouble("lng");
                lat = json.getJSONObject("result").getJSONObject("location").getDouble("lat");
                jsonArray.add(lng);
                jsonArray.add(lat);
                return jsonArray;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        jsonArray.add(0);
        jsonArray.add(0);
        return jsonArray;
    }
}
